{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading spark-stubs, spark-hive\n",
      "Adding Hive conf dir /opt/hive/conf to classpath\n",
      "Creating SparkSession\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "SLF4J: No SLF4J providers were found.\n",
      "SLF4J: Defaulting to no-operation (NOP) logger implementation\n",
      "SLF4J: See https://www.slf4j.org/codes.html#noProviders for further details.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<a target=\"_blank\" href=\"http://jupyter:4040\">Spark UI</a>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[32mimport \u001b[39m\u001b[36m$ivy.$                                  \n",
       "\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.log4j.{Level, Logger}\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.types._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.functions._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.expressions.Window\n",
       "\n",
       "\u001b[39m\n",
       "\u001b[36mspark\u001b[39m: \u001b[32mSparkSession\u001b[39m = org.apache.spark.sql.SparkSession@77912764"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import $ivy.`org.apache.spark::spark-sql:3.4.0`\n",
    "\n",
    "import org.apache.log4j.{Level, Logger}\n",
    "Logger.getLogger(\"org\").setLevel(Level.OFF)\n",
    "\n",
    "import org.apache.spark._\n",
    "import org.apache.spark.sql._\n",
    "import org.apache.spark.sql.types._\n",
    "import org.apache.spark.sql.functions._\n",
    "import org.apache.spark.sql.expressions.Window\n",
    "\n",
    "val spark = {\n",
    "    NotebookSparkSession.builder()\n",
    "    .progress(false)\n",
    "    .appName(\"app15-3\")\n",
    "    // .master(\"spark://192.168.31.31:7077\")\n",
    "    .master(\"local[*]\")\n",
    "    .config(\"spark.sql.warehouse.dir\", \n",
    "            \"hdfs://192.168.31.31:9000/user/hive/warehouse\") \n",
    "    .config(\"spark.cores.max\", \"4\") \n",
    "    .config(\"spark.executor.instances\", \"1\") \n",
    "    .config(\"spark.executor.cores\", \"2\") \n",
    "    .config(\"spark.executor.memory\", \"10g\") \n",
    "    .config(\"spark.shuffle.service.enabled\", \"false\") \n",
    "    .config(\"spark.dynamicAllocation.enabled\", \"false\") \n",
    "    .config(\"spark.sql.catalogImplementation\", \"hive\")\n",
    "    .config(\"spark.sql.repl.eagerEval.enabled\", \"true\")\n",
    "    .config(\"spark.driver.allowMultipleContexts\", \"true\")\n",
    "    .getOrCreate()\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[32mimport \u001b[39m\u001b[36mspark.implicits._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36mspark.sqlContext.implicits._\n",
       "\u001b[39m\n",
       "defined \u001b[32mfunction\u001b[39m \u001b[36msc\u001b[39m\n",
       "\u001b[36mhiveCxt\u001b[39m: \u001b[32msql\u001b[39m.\u001b[32mhive\u001b[39m.\u001b[32mHiveContext\u001b[39m = org.apache.spark.sql.hive.HiveContext@16f97e43"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import spark.implicits._\n",
    "import spark.sqlContext.implicits._\n",
    "def sc = spark.sparkContext\n",
    "val hiveCxt = new org.apache.spark.sql.hive.HiveContext(sc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defined \u001b[32mclass\u001b[39m \u001b[36mRichDF\u001b[39m"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Credit to Aivean\n",
    "implicit class RichDF(val ds:DataFrame) {\n",
    "    def showHTML(limit: Int = 50, truncate: Int = 100) = {\n",
    "        import xml.Utility.escape\n",
    "        val data = ds.take(limit)\n",
    "        val header = ds.schema.fieldNames.toSeq        \n",
    "        val rows: Seq[Seq[String]] = data.map { row =>\n",
    "          row.toSeq.map {cell =>\n",
    "            val str = cell match {\n",
    "              case null => \"null\"\n",
    "              case binary: Array[Byte] => binary.map(\"%02X\".format(_)).mkString(\"[\", \" \", \"]\")\n",
    "              case array: Array[_] => array.mkString(\"[\", \", \", \"]\")\n",
    "              case seq: Seq[_] => seq.mkString(\"[\", \", \", \"]\")\n",
    "              case _ => cell.toString\n",
    "            }\n",
    "            if (truncate > 0 && str.length > truncate) {\n",
    "              // do not show ellipses for strings shorter than 4 characters.\n",
    "              if (truncate < 4) str.substring(0, truncate)\n",
    "              else str.substring(0, truncate - 3) + \"...\"\n",
    "            } else {\n",
    "              str\n",
    "            }\n",
    "          }: Seq[String]\n",
    "        }\n",
    "    publish.html(s\"\"\" <table>\n",
    "                <tr>\n",
    "                 ${header.map(h => s\"<th>${escape(h)}</th>\").mkString}\n",
    "                </tr>\n",
    "                ${rows.map {row =>\n",
    "                  s\"<tr>${row.map{c => s\"<td>${escape(c)}</td>\" }.mkString}</tr>\"\n",
    "                }.mkString}\n",
    "            </table>\n",
    "        \"\"\")\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[36mut_staff\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, name: string]\n",
       "\u001b[36mut_student\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, name: string ... 2 more fields]\n",
       "\u001b[36mut_event\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, modle: string ... 5 more fields]\n",
       "\u001b[36mut_room\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, name: string ... 2 more fields]\n",
       "\u001b[36mut_attends\u001b[39m: \u001b[32mDataFrame\u001b[39m = [student: string, event: string]\n",
       "\u001b[36mut_teaches\u001b[39m: \u001b[32mDataFrame\u001b[39m = [staff: string, event: string]\n",
       "\u001b[36mut_occurs\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, week: string]\n",
       "\u001b[36mut_modle\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, name: string]\n",
       "\u001b[36mut_week\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, wkstart: string]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val ut_staff = hiveCxt.table(\"sqlzoo.ut_staff\")\n",
    "val ut_student = hiveCxt.table(\"sqlzoo.ut_student\")\n",
    "val ut_event = hiveCxt.table(\"sqlzoo.ut_event\")\n",
    "val ut_room = hiveCxt.table(\"sqlzoo.ut_room\")\n",
    "val ut_attends = hiveCxt.table(\"sqlzoo.ut_attends\")\n",
    "val ut_teaches = hiveCxt.table(\"sqlzoo.ut_teaches\")\n",
    "val ut_occurs = hiveCxt.table(\"sqlzoo.ut_occurs\")\n",
    "val ut_modle = hiveCxt.table(\"sqlzoo.ut_modle\")\n",
    "val ut_week = hiveCxt.table(\"sqlzoo.ut_week\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**co.CHt is to be given all the teaching that co.ACg currently does. Identify those events which will clash.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>co.Cht</th><th>co.ACg</th>\n",
       "                </tr>\n",
       "                <tr><td>co12005.T03</td><td>co12005.T01</td></tr><tr><td>co12005.T03</td><td>co72013.L02</td></tr><tr><td>co12005.T03</td><td>co72013.T03</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, modle: string ... 9 more fields]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val t = (ut_event.withColumnRenamed(\"id\", \"event\")\n",
    "     .join(ut_teaches, \"event\")\n",
    "     .join(ut_occurs, \"event\")\n",
    "     .withColumn(\"begin\", to_timestamp(col(\"tod\"), \"HH:mm\"))\n",
    "     .withColumn(\"end\", (unix_timestamp(col(\"tod\"), \"HH:mm\") + \n",
    "                 (lit(3600) * col(\"duration\"))).cast(\"timestamp\")))\n",
    "\n",
    "(t.filter(col(\"staff\") === \"co.CHt\").alias(\"t1\")\n",
    " .join(t.filter(col(\"staff\") === \"co.ACg\").alias(\"t2\"), \n",
    "       Seq(\"week\", \"dow\"))\n",
    " .filter(((col(\"t1.begin\") >= col(\"t2.begin\")) && \n",
    "          (col(\"t1.begin\") < col(\"t2.end\"))) ||\n",
    "         ((col(\"t2.begin\") >= col(\"t1.begin\")) && \n",
    "          (col(\"t2.begin\") < col(\"t1.end\"))))\n",
    " .select(col(\"t1.event\").alias(\"co.Cht\"), \n",
    "         col(\"t2.event\").alias(\"co.ACg\"))\n",
    " .distinct()\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Produce a table showing the utilisation rate and the occupancy level for all rooms with a capacity more than 60.**\n",
    "\n",
    "> I don't know how \"utilisation rate\" and \"occupancy level\" are defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+-------+\n",
      "| id|   wkstart|max_hrs|\n",
      "+---+----------+-------+\n",
      "| 01|2018-10-01|     60|\n",
      "| 02|2018-10-08|     60|\n",
      "| 03|2018-10-15|     60|\n",
      "| 04|2018-10-22|     60|\n",
      "| 05|2018-10-29|     60|\n",
      "| 06|2018-11-05|     60|\n",
      "| 07|2018-11-12|     60|\n",
      "| 08|2018-11-19|     60|\n",
      "| 09|2018-11-26|     60|\n",
      "| 10|2018-12-03|     60|\n",
      "| 11|2018-12-10|     60|\n",
      "| 12|2018-12-17|     60|\n",
      "| 13|2019-01-07|     60|\n",
      "| 14|2019-01-14|     60|\n",
      "| 15|2019-01-21|     60|\n",
      "+---+----------+-------+\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>room</th><th>util</th><th>occup_level</th>\n",
       "                </tr>\n",
       "                <tr><td>cr.SMH</td><td>0.24</td><td>0.2745744680851064</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[36moc_time\u001b[39m: \u001b[32mDataFrame\u001b[39m = [max(close_time): timestamp, min(open_time): timestamp ... 1 more field]\n",
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [id: string, wkstart: string ... 1 more field]\n",
       "\u001b[36ms\u001b[39m: \u001b[32mDataFrame\u001b[39m = [room: string, sum(sze): bigint ... 1 more field]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// theoretical full utilisation hours: 15 weeks * 5 days * 12 hours\n",
    "val oc_time = (\n",
    "    ut_event\n",
    "       .withColumn(\"close_time\", \n",
    "                   (unix_timestamp(col(\"tod\"), \"HH:mm\") + \n",
    "                   lit(3600) * col(\"duration\")).cast(\"timestamp\"))\n",
    "       .withColumn(\"open_time\", \n",
    "                   to_timestamp(col(\"tod\"), \"HH:mm\"))\n",
    "      .agg(max(\"close_time\"), min(\"open_time\"))\n",
    "      .withColumn(\"max_hrs\", ($\"max(close_time)\"-$\"min(open_time)\")\n",
    "                  .cast(\"long\")/lit(3600)))\n",
    "\n",
    "val t = (ut_week.join(oc_time.select(\"max_hrs\"))\n",
    "         .withColumn(\"max_hrs\", lit(5) * $\"max_hrs\".cast(\"long\")))\n",
    "t.show()\n",
    "\n",
    "// attended students per event\n",
    "val s = (ut_event.withColumnRenamed(\"id\", \"event\")\n",
    "     .join(ut_occurs, \"event\")\n",
    "     .join(ut_attends, \"event\")\n",
    "     .join(ut_student, (ut_attends(\"student\") === ut_student(\"id\")))\n",
    "     .join(ut_room, (ut_event(\"room\") === ut_room(\"id\")))\n",
    "     .groupBy(\"room\")\n",
    "     .agg(sum(\"sze\"), sum(\"capacity\")))\n",
    "\n",
    "(ut_event.withColumnRenamed(\"id\", \"event\")\n",
    " .join(ut_occurs, \"event\")\n",
    " .join(ut_room.filter(col(\"capacity\") > 60), \n",
    "       (ut_event(\"room\") === ut_room(\"id\")))\n",
    " .groupBy(\"room\")\n",
    " .sum(\"duration\")\n",
    " .join(s, \"room\")\n",
    " .withColumn(\"util\", col(\"sum(duration)\") /\n",
    "             t.groupBy().sum(\"max_hrs\").first.getLong(0))\n",
    " .withColumn(\"occup_level\", col(\"sum(sze)\")/col(\"sum(capacity)\"))\n",
    " .select(\"room\", \"util\", \"occup_level\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**A one hour staff meeting is to be held between 09:00 and 17:00. Events which clash are to be cancelled. Identify the hour which will result in the least disruption.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>dow</th><th>hour</th><th>01</th><th>02</th><th>03</th><th>04</th><th>05</th><th>06</th><th>07</th><th>08</th><th>10</th><th>11</th><th>12</th><th>13</th>\n",
       "                </tr>\n",
       "                <tr><td>1</td><td>9</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>1</td><td>10</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>1</td><td>11</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>1</td><td>12</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td></tr><tr><td>1</td><td>13</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>1</td><td>14</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>1</td><td>15</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>1</td><td>16</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td><td>8</td></tr><tr><td>1</td><td>17</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>2</td><td>9</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>2</td><td>10</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>2</td><td>11</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td><td>9</td></tr><tr><td>2</td><td>12</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td></tr><tr><td>2</td><td>13</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>2</td><td>14</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>2</td><td>15</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>2</td><td>16</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>2</td><td>17</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>2</td><td>19</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>2</td><td>20</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>3</td><td>9</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>3</td><td>10</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>3</td><td>11</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>3</td><td>12</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>3</td><td>13</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>3</td><td>14</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>3</td><td>15</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>3</td><td>16</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>3</td><td>17</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>4</td><td>9</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>4</td><td>10</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td></tr><tr><td>4</td><td>11</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>4</td><td>12</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>4</td><td>13</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>4</td><td>14</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td></tr><tr><td>4</td><td>15</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>4</td><td>16</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>7</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>4</td><td>17</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>5</td><td>9</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>5</td><td>10</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>5</td><td>11</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td><td>5</td></tr><tr><td>5</td><td>12</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td><td>4</td></tr><tr><td>5</td><td>13</td><td>3</td><td>3</td><td>3</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td></tr><tr><td>5</td><td>14</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>5</td><td>15</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>2</td><td>3</td><td>3</td><td>3</td><td>3</td></tr><tr><td>5</td><td>16</td><td>7</td><td>7</td><td>7</td><td>7</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td><td>6</td></tr><tr><td>5</td><td>17</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"jp-RenderedText\">\n",
       "<pre><code><span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">t</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span> = [event: string, week: string ... 3 more fields]\n",
       "<span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">dfs</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">Seq</span></span>[<span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span>] = <span style=\"color: yellow\"><span class=\"ansi-yellow-fg\">List</span></span>(\n",
       "  [event: string, week: string ... 3 more fields],\n",
       "  [event: string, week: string ... 3 more fields]\n",
       ")\n",
       "<span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">df</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span> = [event: string, week: string ... 3 more fields]</code></pre>\n",
       "</div>"
      ],
      "text/plain": [
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, week: string ... 3 more fields]\n",
       "\u001b[36mdfs\u001b[39m: \u001b[32mSeq\u001b[39m[\u001b[32mDataFrame\u001b[39m] = \u001b[33mList\u001b[39m(\n",
       "  [event: string, week: string ... 3 more fields],\n",
       "  [event: string, week: string ... 3 more fields]\n",
       ")\n",
       "\u001b[36mdf\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, week: string ... 3 more fields]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "val t = (ut_event.withColumn(\"tod\", to_timestamp(ut_event(\"tod\"), \"HH:mm\"))\n",
    "         .withColumn(\"duration\", ut_event(\"duration\").cast(\"integer\"))\n",
    "         .join(ut_occurs, (ut_event(\"id\") === ut_occurs(\"event\")))\n",
    "         .select(\"event\", \"week\", \"dow\", \"tod\", \"duration\"))\n",
    "\n",
    "var dfs = Seq[DataFrame]()\n",
    "for (i <- 1 to 10){\n",
    "    val t_ = t.filter(t(\"duration\") === i)\n",
    "    if (t_.count() > 0) {\n",
    "        val t__ = t_.withColumn(\"tod\", t_(\"tod\") + expr(\n",
    "            \"interval \" + (i-1).toString + \" hours\"))\n",
    "        dfs = dfs :+ t__\n",
    "    }\n",
    "}\n",
    "val df = dfs.reduce(_.union(_))\n",
    "\n",
    "(df.na.replace(\"dow\", Map(\"Monday\" -> \"1\", \"Tuesday\" -> \"2\", \"Wednesday\" -> \"3\", \n",
    "                          \"Thursday\" -> \"4\", \"Friday\" -> \"5\"))\n",
    " .withColumn(\"hour\", hour(col(\"tod\")))\n",
    " .groupBy(\"dow\", \"hour\")\n",
    " .pivot(\"week\")\n",
    " .count()\n",
    " .orderBy(\"dow\", \"hour\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>tod</th><th>dow</th><th>event</th>\n",
       "                </tr>\n",
       "                <tr><td>09:00</td><td>Monday</td><td>12</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "// You may find that week \"09\", \"14\", \"15\" are totally vacant\n",
    "// or you can choose Mon 9am/12pm, Tue 5pm, Thu 9am, Fri 5pm\n",
    "\n",
    "(df.withColumn(\"tod\", date_format($\"tod\", \"HH:mm\"))\n",
    " .filter(hour(col(\"tod\")).between(9, 17))\n",
    " .groupBy(\"tod\", \"dow\")\n",
    " .agg(count(\"event\").alias(\"event\"))\n",
    " .orderBy(\"event\", \"tod\", \"dow\")\n",
    " .limit(1)\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Find all clashes - include the events which clash and the staff, student or rooms that they have in common.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>event1</th><th>event2</th><th>room</th><th>week</th><th>dow</th>\n",
       "                </tr>\n",
       "                <tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>01</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>11</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>12</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>02</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>02</td><td>Friday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>07</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>08</td><td>Tuesday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>03</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>06</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>10</td><td>Tuesday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>12</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>03</td><td>Friday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>04</td><td>Friday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>13</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>04</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>08</td><td>Friday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>10</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>01</td><td>Tuesday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>05</td><td>Tuesday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>06</td><td>Tuesday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>07</td><td>Tuesday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>11</td><td>Friday</td></tr><tr><td>co22020.T04</td><td>co22007.T01</td><td>co.LB47</td><td>05</td><td>Friday</td></tr><tr><td>coh8412615.T03</td><td>co22008.T04</td><td>co.117+118</td><td>13</td><td>Tuesday</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, modle: string ... 8 more fields]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val t = (ut_event\n",
    "     .withColumn(\"start\", to_timestamp(ut_event(\"tod\"), \"HH:mm\"))\n",
    "     .withColumnRenamed(\"id\", \"event\")\n",
    "     .join(ut_occurs, \"event\")\n",
    "     .withColumn(\"end\", (unix_timestamp(col(\"start\")) + lit(3600)*col(\"duration\"))\n",
    "                 .cast(\"timestamp\")))\n",
    "(t.alias(\"t1\")\n",
    " .join(t.alias(\"t2\"), Seq(\"week\", \"dow\", \"room\"))\n",
    " .filter(col(\"t1.event\") !== col(\"t2.event\"))\n",
    " .filter((((col(\"t1.start\") >= col(\"t2.start\")) &&\n",
    "           (col(\"t1.start\") < col(\"t2.end\"))) ||\n",
    "          ((col(\"t2.start\") >= col(\"t1.start\")) &&\n",
    "           (col(\"t2.start\") < col(\"t1.end\")))) &&\n",
    "         (col(\"t1.event\") > col(\"t2.event\")))\n",
    " .select(col(\"t1.event\").alias(\"event1\"),\n",
    "         col(\"t2.event\").alias(\"event2\"),\n",
    "         col(\"room\"), col(\"week\"), col(\"dow\"))\n",
    " .distinct()\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Produce a timetable for a group of full time students for week 1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>dow</th><th>student</th><th>9</th><th>10</th><th>11</th><th>12</th><th>13</th><th>14</th><th>15</th><th>16</th><th>17</th><th>19</th><th>20</th>\n",
       "                </tr>\n",
       "                <tr><td>Friday</td><td>co.12008.Eb</td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co.22022.E</td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co1.CO</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.CO</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.CO.a</td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.CO.b</td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.CO.e</td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.NT.Ea</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.NT.Eb</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.NT.Ec</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.NT.Ed</td><td></td><td></td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co2.NT.Ee</td><td></td><td>Y</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co3.DMM</td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co3.DNC</td><td></td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co3.DST</td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.CO</td><td></td><td></td><td></td><td></td><td>Y</td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.DNC</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.IS</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.MM</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.ND</td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>co4.SE</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND1</td><td>Y</td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND1a</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND1b</td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND1c</td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND2a</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>coh.HND2b</td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.IS.d</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.IS.e</td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.ITeC.feb</td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.ITeC.pt2</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td></tr><tr><td>Friday</td><td>com.MM.a</td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.MM.d</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td>com.MM.pt2</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co.12008.Ea</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.BAe</td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.a</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.c</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.d</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.e</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.f</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.h</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.i</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.CO.j</td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co1.SE.pt</td><td></td><td>Y</td><td></td><td>Y</td><td></td><td>Y</td><td></td><td>Y</td><td>Y</td><td></td><td></td></tr><tr><td>Monday</td><td>co2.CO</td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co2.CO.a</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co2.CO.b</td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td>Y</td><td></td><td></td><td></td></tr><tr><td>Monday</td><td>co2.CO.c</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Y</td><td></td><td></td><td></td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"jp-RenderedText\">\n",
       "<pre><code><span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">t</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span> = [event: string, modle: string ... 7 more fields]\n",
       "<span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">dfs</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">Seq</span></span>[<span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span>] = <span style=\"color: yellow\"><span class=\"ansi-yellow-fg\">List</span></span>(\n",
       "  [event: string, modle: string ... 7 more fields],\n",
       "  [event: string, modle: string ... 7 more fields]\n",
       ")\n",
       "<span style=\"color: rgb(0, 187, 187)\"><span class=\"ansi-cyan-fg\">df</span></span>: <span style=\"color: rgb(0, 187, 0)\"><span class=\"ansi-green-fg\">DataFrame</span></span> = [event: string, modle: string ... 7 more fields]</code></pre>\n",
       "</div>"
      ],
      "text/plain": [
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, modle: string ... 7 more fields]\n",
       "\u001b[36mdfs\u001b[39m: \u001b[32mSeq\u001b[39m[\u001b[32mDataFrame\u001b[39m] = \u001b[33mList\u001b[39m(\n",
       "  [event: string, modle: string ... 7 more fields],\n",
       "  [event: string, modle: string ... 7 more fields]\n",
       ")\n",
       "\u001b[36mdf\u001b[39m: \u001b[32mDataFrame\u001b[39m = [event: string, modle: string ... 7 more fields]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "val t = (ut_event\n",
    "     .withColumn(\"tod\", to_timestamp(ut_event(\"tod\"), \"HH:mm\"))\n",
    "     .withColumnRenamed(\"id\", \"event\")\n",
    "     .join(ut_occurs.filter(col(\"week\") === \"01\"), \"event\")\n",
    "     .join(ut_attends, \"event\"))\n",
    "\n",
    "var dfs = Seq[DataFrame]()\n",
    "for (i <- 1 to 10){\n",
    "    val t_ = t.filter(t(\"duration\") === i)\n",
    "    if (t_.count() > 0){\n",
    "        val t__ = t_.withColumn(\"tod\", t_(\"tod\") + expr(\n",
    "            \"interval \" + (i-1).toString + \" hours\"))\n",
    "        dfs = dfs :+ t__\n",
    "    }\n",
    "}\n",
    "val df = dfs.reduce(_.union(_))\n",
    "\n",
    "(df.withColumn(\"tod\", hour(col(\"tod\")))\n",
    " .withColumn(\"flag\", lit(\"Y\"))\n",
    " .groupBy(\"dow\", \"student\")\n",
    " .pivot(\"tod\")\n",
    " .agg(first(\"flag\"))\n",
    " .na.fill(\"\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Scala",
   "language": "scala",
   "name": "scala"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".sc",
   "mimetype": "text/x-scala",
   "name": "scala",
   "nbconvert_exporter": "script",
   "version": "2.12.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
